package com.xcyy.clbzbh.repository;

import com.xcyy.clbzbh.entity.Benchmark;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface BenchmarkRepository extends JpaRepository<Benchmark,String> , JpaSpecificationExecutor<Benchmark> {
    List<Benchmark> findByName(String name);
    List<Benchmark> findByPointID(String pointId);
    Page<Benchmark> findByCity(String city, Pageable pageable);
    Page<Benchmark> findByCityAndCountry(String city,String country, Pageable pageable);
    List<Benchmark> findByCountry(String country);
    @Query(value = "select sum(IF(STATUS='已入库',1,0)) rkwcs ,sum(IF(STATUS='分发',1,0)) ffwcs ,sum(IF(STATUS='已提交',1,0)) tjwcs ,sum(IF(STATUS='已审核',1,0)) shwcs ,sum(IF(STATUS='驳回',1,0)) bhwcs ,COUNT(*) zs  FROM benchmark",nativeQuery = true)
    Object totalbenchmark();

    @Query(value = "select city,sum(shwcs),sum(zs) from(select city, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM gpspoint GROUP BY city union all select city, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM benchmark GROUP BY city )  t group by city",nativeQuery = true)
    List<Object> countCity();

    @Query(value = "select country,sum(shwcs),sum(zs) from(select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM gpspoint where city=?1 GROUP BY country union all select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM benchmark where city=?1 GROUP BY country ) t group by country",nativeQuery = true)
    List<Object> countCountry(String city);

    @Query(value = "select country,sum(shwcs),sum(zs) from(select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM gpspoint  GROUP BY country union all select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM benchmark  GROUP BY country ) t group by country", nativeQuery = true,countQuery = "SELECT COUNT(1) FROM ( select country,sum(shwcs),sum(zs) from(select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM gpspoint  GROUP BY country union all select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM benchmark  GROUP BY country ) t group by country ) s ")
    Page<Object> countAllCountry(Pageable pageable);

    @Query(value = "select country,sum(shwcs),sum(zs) from(select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM gpspoint where country=?1 GROUP BY country union all select country, sum(IF(STATUS='已审核',1,0)) shwcs ,COUNT(*) zs  FROM benchmark where country=?1 GROUP BY country ) t group by country",nativeQuery = true)
    List<Object> countByName(String country);

}
